using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Look.Imagem.Entidades;

namespace MakeIT.Look.Imagem.Negocios
{
	public partial class LookDAOT : DAOT
	{

		public PlaylistVO getPlaylist(int? idPlayList, string playlist)
		{
			PlaylistVO playlistVO = null;
			List<PlaylistVO> listPlaylistVOs = getPlaylists(idPlayList, playlist, null);
			if (listPlaylistVOs != null && listPlaylistVOs.Count > 0)
			{
				playlistVO = listPlaylistVOs[0];
			}
			return playlistVO;
		}

		public PlaylistVO getPlaylist(int? idPlayList)
		{
			return getPlaylist(idPlayList, null);
		}

		public List<PlaylistVO> getPlaylists()
		{
			return getPlaylists(null, null, null);
		}

		public List<PlaylistVO> getPlaylists(int? idPlayList)
		{
			return getPlaylists(idPlayList, null, null);
		}

		public List<PlaylistVO> getPlaylists(int? idPlayList, string playlist, bool? ativo)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT Pll.idPlayList as Pll_idPlayList, Pll.playlist as Pll_playlist, Pll.ativo as Pll_ativo, Pll.cadastro as Pll_cadastro, Pll.cadastradoPor as Pll_cadastradoPor, Pll.edicao as Pll_edicao, Pll.editadoPor as Pll_editadoPor  ");
			query.AppendLine("FROM playlist as Pll ");
			if (idPlayList != null)
			{
				query.AppendLine(where + "Pll.idPlayList=@idPlayList ");
				where = "AND ";
			}
			if (playlist != null)
			{
				query.AppendLine(where + "Pll.playlist LIKE @playlist ");
				where = "AND ";
			}
			if (ativo != null)
			{
				query.AppendLine(where + "Pll.ativo=@ativo ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY Pll.playlist ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			if (idPlayList!= null) cmd.Parameters.Add(getParameter(cmd, "@idPlayList", idPlayList));
			if (playlist!= null) cmd.Parameters.Add(getParameter(cmd, "@playlist", "%"+playlist+"%"));
			if (ativo!= null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value?"S":"N"));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<PlaylistVO> listPlaylist = new List<PlaylistVO>();

				while (reader.Read())
				{
					PlaylistVO playlistVO = PlaylistVO.CreateVO();
					playlistVO.idPlayList = reader.GetInt32(reader.GetOrdinal("Pll_idPlayList"));
					playlistVO.playlist = reader.GetString(reader.GetOrdinal("Pll_playlist"));
					playlistVO.ativo = reader.GetString(reader.GetOrdinal("Pll_ativo")).Equals("S");
					playlistVO.cadastro = reader.GetDateTime(reader.GetOrdinal("Pll_cadastro"));
					playlistVO.cadastradoPor = reader.GetString(reader.GetOrdinal("Pll_cadastradoPor"));
					playlistVO.edicao = reader.GetDateTime(reader.GetOrdinal("Pll_edicao"));
					playlistVO.editadoPor = reader.GetString(reader.GetOrdinal("Pll_editadoPor"));


					listPlaylist.Add(playlistVO);
				}
				reader.Close();
				return listPlaylist;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void setPlaylist(PlaylistVO playlistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE playlist ");
			query.AppendLine("SET playlist=@playlist, ativo=@ativo,  cadastro=@cadastro, cadastradoPor=@cadastradoPor, edicao=@edicao, editadoPor=@editadoPor ");
			query.AppendLine("WHERE idPlayList=@idPlayList ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlayList", playlistVO.idPlayList));
			cmd.Parameters.Add(getParameter(cmd, "@playlist", playlistVO.playlist));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (playlistVO.ativo==null?" ":(playlistVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", playlistVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", playlistVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", playlistVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", playlistVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}

		public long newPlaylist(PlaylistVO playlistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO playlist ");
			query.AppendLine("  ( playlist, ativo, cadastro, cadastradoPor, edicao, editadoPor ) ");
			query.AppendLine("VALUES ( @playlist, @ativo, @cadastro, @cadastradoPor, @edicao, @editadoPor ) ");

			DbCommand cmd = this.getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@playlist", playlistVO.playlist));
			cmd.Parameters.Add(getParameter(cmd, "@ativo", (playlistVO.ativo==null?" ":(playlistVO.ativo.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@cadastro", playlistVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@cadastradoPor", playlistVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@edicao", playlistVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@editadoPor", playlistVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('playlist') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }

		}

		public void delPlaylist(PlaylistVO playlistVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM playlist ");
			query.AppendLine("WHERE idPlayList=@idPlayList ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idPlayList", playlistVO.idPlayList));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }

		}
	}
}
